import sys
sys.path.append(r"G:/Code/ac_lingxing_api/ad_ana")

from db.mysql_db import mysql_db
import pandas as pd

class top10_kw:
    
    def __init__(self):
        self.engine_kw = mysql_db("keyword")
        self.engine_base_data = mysql_db("ac_base_data")
    
    
    def get_data_by_asin_lst(self, asin_lst:list):
        """
            通过 asin.查询该asin下的前10名数据
            @asin_lst:需要查询的asin列表
        """
        # 把列表转换成 数据库查询的字符串
        asin_str = ",".join(["'" + str(asin) + "'" for asin in asin_lst])
        sql = f"select distinct asin, keyword from top10_kw where asin in ({asin_str})"
        kw_data = self.engine_kw.read_data_by_pd(sql)
        return kw_data

    def get_asin_by_product_name(self, product_name_lst:list, country='美国'):
        """
        通过品名,获取到对应的asin
        @product_name_lst: 需要查询的品名列表
        """
        # 把列表转换成 数据库查询的字符串
        product_name_str = ",".join(["'" + str(product_name) + "'" for product_name in product_name_lst])
        sql = f"select local_name, asin, marketplace, sid from lingxing_listing where local_name in ({product_name_str}) and marketplace = '{country}'"
        product_df = self.engine_base_data.read_data_by_pd(sql)
        return product_df

    def get_keyword_by_product_name(self, product_name_lst: list):
        """
        通过品名, 获取该品名下所有asin的top10的关键词
        """
        product_df = self.get_asin_by_product_name(product_name_lst)
        asin_lst = list(product_df['asin'])
        kw_df = self.get_data_by_asin_lst(asin_lst)
        product_name_top10 = pd.merge(product_df, kw_df)
        product_name_top10 = product_name_top10[['local_name', 'marketplace', 'keyword']].drop_duplicates()
        
        return product_name_top10
        




